﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Ruandao.MiniAccess.OrMap;
using System.Data;

namespace Ruandao.MiniAccess.Sqlite {
    /// <summary>
    /// 根据OR映射数据创建SQLite中的SQL语句。
    /// </summary>
   public class SqlBuilder 
   {
       

	   #region 创建SQL语句的公有成员

	   /// <summary>
	   /// 构建插入记录的Insert语句。
	   /// </summary>
	   /// <param name="tableName">表名。</param>
	   /// <param name="columnMaps">提供OR映射信息。</param>
	   /// <returns>INSERT 语句的文本。</returns>
	   public static string BuildInsertSql(string tableName, IList<ColumnMap> columnMaps)
	   {
		   StringBuilder sb = new StringBuilder();
		   sb.Append("INSERT INTO [" + tableName + "] ( ");

		   // insert 列表
		   bool isFirst = true;
		   foreach (ColumnMap cm in columnMaps)
		   {
			   if (cm.IsInsert)
			   {
				   if (!isFirst) sb.Append(" , ");
				   sb.Append(" " + ColumnNameString(cm.ColumnName));
				   if (isFirst) isFirst = false;
			   }
		   }
		   sb.Append(" ) VALUES ( ");

		   // 值的列表
		   isFirst = true;
		   foreach (ColumnMap cm in columnMaps)
		   {
			   if (cm.IsInsert)
			   {
				   if (!isFirst) sb.Append(" , ");
				   sb.Append("  " + ParameterString(cm.ColumnName));
				   if (isFirst) isFirst = false;
			   }
		   }
		   sb.Append(" ) ");

		   return sb.ToString();
	   }

	   /// <summary>
	   /// 构建根据主键值删除一条记录的DELETE语句。
	   /// </summary>
	   /// <param name="tableName">表名。</param>
	   /// <param name="columnMaps">提供OR映射信息。</param>
	   /// <returns>DELETE语句的文本。</returns>
	   /// <remarks>
	   /// 如果表没有主键，将抛出异常。
	   /// </remarks>
	   public static string BuildDeleteSql(string tableName, IList<ColumnMap> columnMaps)
	   {
		   StringBuilder sb = new StringBuilder();
		   sb.Append("DELETE");

		   sb.Append(" FROM [" + tableName + "] ");
		   sb.Append(WhereByKeysString(tableName, columnMaps));
		   return sb.ToString();
	   }

	   /// <summary>
	   /// 构建根据条件删除数据库记录的DELETE语句。
	   /// </summary>
	   /// <param name="tableName">表名。</param>
	   /// <param name="columnMaps">提供OR映射信息。</param>
	   /// <returns>DELETE语句的文本。</returns>
	   public static string BuildDeleteByConditionsSql(string tableName, IList<ColumnMap> columnMaps)
	   {
		   StringBuilder sb = new StringBuilder();
		   sb.Append("DELETE ");
		   sb.Append(" FROM [" + tableName + "] '  +  where ");
		   string prefix = "";
		   bool isFirst = true;
		   foreach (ColumnMap cm in columnMaps)
		   {
			   if (!isFirst) sb.Append(", ");
			   if (cm.ColumnMapType == ColumnMapType.PrimaryKeyMap)
			   {
				   prefix = "New_";
			   }
			   else
			   {
				   prefix = "";
			   }
			   sb.Append(" " + ColumnNameString(cm.ColumnName) + " = " + ParameterString(cm.ColumnName, prefix));
			   if (isFirst) isFirst = false;
		   }
		   return sb.ToString();
	   }

	   /// <summary>
	   /// 构建根据主键更新记录的UPDATE语句。
	   /// </summary>
	   /// <param name="tableName">表名。</param>
	   /// <param name="columnMaps">提供OR映射信息。</param>
	   /// <returns>UPDATE语句的文本。</returns>
	   /// <remarks>
	   /// 如果表没有主键，或者表中没有可更新的字段，都将抛出异常。
	   /// </remarks>
	   public static string BuildUpdateSql(string tableName, IList<ColumnMap> columnMaps)
	   {
		   StringBuilder sb = new StringBuilder();
		   sb.Append("UPDATE" + " [" + tableName + "] SET ");

		   string prefix = "";
		   bool isFirst = true;
		   foreach (ColumnMap cm in columnMaps)
		   {
			   if (cm.IsUpdate)
			   {
				   if (!isFirst) sb.Append(", ");
				   if (cm.ColumnMapType == ColumnMapType.PrimaryKeyMap)
				   {
					   prefix = "New_";
				   }
				   else
				   {
					   prefix = "";
				   }
				   sb.Append(" " + ColumnNameString(cm.ColumnName) + " = " + ParameterString(cm.ColumnName, prefix));
				   if (isFirst) isFirst = false;
			   }
		   }

		   if (isFirst == true)
		   {
			   throw new OrMapException("表 <" + tableName + "> 没有可以更新的字段。");
		   }

		   sb.Append(" ");
		   sb.Append(WhereByKeysString(tableName, columnMaps));
		   return sb.ToString();
	   }

	   /// <summary>
	   /// 根据主键查找记录的SQL 语句。
	   /// </summary>
	   /// <param name="tableName">表名。</param>
	   /// <param name="columnMaps">提供OR映射信息。</param>
	   /// <returns>SELECT 语句的文本。</returns>
	   /// <remarks>
	   /// 如果表没有主键，将抛出异常。
	   /// </remarks>
	   public static string BuildSelectByKeysSql(string tableName, IList<ColumnMap> columnMaps)
	   {
		   StringBuilder sb = new StringBuilder();
		   sb.Append(GetSelectString(tableName, columnMaps));

		   sb.Append("FROM [" + tableName + "] ");
		   sb.Append(WhereByKeysString(tableName, columnMaps));
		   return sb.ToString();
	   }

	   /// <summary>
	   /// 构造按条件查询的Select语句。
	   /// </summary>
	   /// <param name="tableName">表名。</param>
	   /// <param name="columnMaps">提供OR映射信息。</param>
	   /// <returns>SELECT 语句的文本。</returns>
	   /// <remarks>条件参数的参数名称缺省使用 @Conditions。执行时@Conditions参数的值必须是一个合法的WHERE子句，以及后面可以有连接、OrderBy等字句。</remarks>
	   public static string BuildSelectByConditionsSql(string tableName, IList<ColumnMap> columnMaps)
	   {
		   StringBuilder sb = new StringBuilder();
		   sb.Append(GetSelectString(tableName, columnMaps));
		   string prefix = "";
		   bool isFirst = true;
		   sb.Append(" where ");
		   foreach (ColumnMap cm in columnMaps)
		   {
				   if (!isFirst) sb.Append(", ");
				   if (cm.ColumnMapType == ColumnMapType.PrimaryKeyMap)
				   {
					   prefix = "New_";
				   }
				   else
				   {
					   prefix = "";
				   }
				   sb.Append(" " + ColumnNameString(cm.ColumnName) + " = " + ParameterString(cm.ColumnName, prefix));
				   if (isFirst) isFirst = false;
		   }
		   sb.Append("FROM [" + tableName + "] ' ");
		   return sb.ToString();
	   }

	   #endregion

	   #region 私有工具方法
	   // 字符串开始无空格，字符串以回车换行结尾。
	   private static string GetSelectString(string tableName, IList<ColumnMap> columnMaps)
	   {
		   StringBuilder sb = new StringBuilder();
		   sb.Append("SELECT ");
		   bool isFirst = true;
		   foreach (ColumnMap cm in columnMaps)
		   {
			   if (!isFirst) sb.Append(" , ");
			   sb.Append(ColumnNameString(cm.ColumnName));
			   if (isFirst) isFirst = false;
		   }
		   sb.Append(String.Format(" from [{0}] ", tableName));
		   return sb.ToString();
	   }
	   // 字符串开始无空格，字符串以回车换行结尾。如果没有主键，将抛出异常。
	   private static string WhereByKeysString(string tableName, IList<ColumnMap> columnMaps)
	   {
		   StringBuilder sb = new StringBuilder();
		   sb.Append("WHERE ");
		   bool isFirst = true;
		   foreach (ColumnMap cm in columnMaps)
		   {
			   if (cm.ColumnMapType == ColumnMapType.PrimaryKeyMap)
			   {
				   if (!isFirst) sb.Append(" AND ");
				   sb.Append(" " + ColumnNameString(cm.ColumnName) + " = " + ParameterString(cm.ColumnName));
				   if (isFirst) isFirst = false;
			   }
		   }
		   if (isFirst)
		   {
			   throw new OrMapException("表" + tableName + "没有主键。当使用GetEntity, DeleteEntity和UpdateEntity等函数时，对应的数据库表必须有主键。");
		   }
		   sb.Append(" ");
		   return sb.ToString();
	   }

	   private static string ColumnNameString(string column, string prefix)
	   {
		   return "[" + prefix + column + "]";
	   }

	   private static string ColumnNameString(string column)
	   {
		   return ColumnNameString(column, "");
	   }

	   // 把空格替换为下划线。因为参数名称不支持空格分隔的名称。
	   private static string ParameterString(string column, string prefix)
	   {
		   return ":" + prefix + String.Join("_", column.Split(' '));
	   }

	   private static string ParameterString(string column)
	   {
		   return ParameterString(column, "");
	   }

	   #endregion
    }
}
